﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class HOADONDAO
    {
        ///CỦA AI THÌ TỰ ĐÓNG KHUNG VÀO. TRÁNH XÓA NHẦM CODE
        /////////////////CỦA LINH///////////////////
        public HOADONDTO LayThongTinHD(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH18_LAYTHONGTINHD", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaBan", System.Data.SqlDbType.Int);
            command.Parameters["@MaBan"].Value = BDTO.MaBan;
            SqlDataReader reader = command.ExecuteReader();
            HOADONDTO HDTO = new HOADONDTO();
            if (reader.Read())
            {
                HDTO.MaHD = reader.GetInt32(0);
                HDTO.MaNV = reader.GetInt32(1);
                //HDTO.SoKhach = reader.GetInt32(2);
                //HDTO.MaBan = reader.GetInt32(3);
            }

            conn.Close();
            return HDTO;
        }
        public int CapNhatThongTin(HOADONDTO HDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH17_CAPNHATBAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaHD", System.Data.SqlDbType.Int);
            command.Parameters["@MaHD"].Value = HDTO.MaHD;

            command.Parameters.Add("@MaBanMoi", System.Data.SqlDbType.Int);
            command.Parameters["@MaBanMoi"].Value = HDTO.MaBan;

            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int);
            command.Parameters["@MaNV"].Value = HDTO.MaNV;

            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = HDTO.SoKhach;

            int x = command.ExecuteNonQuery();
            conn.Close();
            return x;
        }
        public int DatBan(HOADONDTO HDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH19_DATBAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaBan", System.Data.SqlDbType.Int);
            command.Parameters["@MaBan"].Value = HDTO.MaBan;

            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int);
            command.Parameters["@MaNV"].Value = HDTO.MaNV;

            command.Parameters.Add("@ThoiGian", System.Data.SqlDbType.DateTime);
            command.Parameters["@ThoiGian"].Value = HDTO.ThoiGian;

            command.Parameters.Add("@SoKhach", System.Data.SqlDbType.Int);
            command.Parameters["@SoKhach"].Value = HDTO.SoKhach;

            int x = command.ExecuteNonQuery();
            conn.Close();
            return x;
        }
        public bool KiemTraHuy(BANDTO BDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH20_KIEMTRAHUYBAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaBan", System.Data.SqlDbType.Int);
            command.Parameters["@MaBan"].Value = BDTO.MaBan;

            int x = Convert.ToInt32(command.ExecuteScalar());
            conn.Close();
            if(x==0)
                return true;
            return false;
        }
        public int HuyBan(HOADONDTO HDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH21_HUYBAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaHD", System.Data.SqlDbType.Int);
            command.Parameters["@MaHD"].Value = HDTO.MaHD;

            int x = command.ExecuteNonQuery();
            conn.Close();
            return x;
        }
        public int ThanhToan(HOADONDTO HDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH33_THANHTOAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaHD", System.Data.SqlDbType.Int);
            command.Parameters["@MaHD"].Value = HDTO.MaHD;

            int x = command.ExecuteNonQuery();
            conn.Close();
            return x;
        }
        public List<HOADONDTO> DSHD(DateTime bd, DateTime kt)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH36_HDCHUATHANHTOAN2", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@TGBatDau", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGBatDau"].Value = bd;

            command.Parameters.Add("@TGKetThuc", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGKetThuc"].Value = kt;

            SqlDataReader reader = command.ExecuteReader();
            List<HOADONDTO> ds = new List<HOADONDTO>();
            while (reader.Read())
            {
                //MaHD,Xoa,TrangThai,'BAN', 'TANG', 'THU', 'VAT'
                HOADONDTO HDTO = new HOADONDTO();
                HDTO.MaHD = reader.GetInt32(0);
                HDTO.Xoa = reader.GetBoolean(1);
                HDTO.TrangThai = reader.GetBoolean(2);
                HDTO.Ban = reader.GetSqlMoney(3).ToString();
                HDTO.Tang = reader.GetSqlMoney(4).ToString();
                HDTO.Thu = reader.GetSqlMoney(5).ToString();
                HDTO.VAT = reader.GetSqlMoney(6).ToString();
                ds.Add(HDTO);
            }
            reader.Close();
            command = new SqlCommand("LINH35_HDCHUATHANHTOAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@TGBatDau", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGBatDau"].Value = bd;

            command.Parameters.Add("@TGKetThuc", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGKetThuc"].Value = kt;

            reader = command.ExecuteReader();
            while (reader.Read())
            {
                //MaHD, TenTang, TenBan, TenNV, ThoiGian, SoKhach from HOADON, TANG, BAN, NHANVIEN
                //MaHD,Xoa,TrangThai,'BAN', 'TANG', 'THU', 'VAT'
                HOADONDTO HDTO = new HOADONDTO();
                HDTO.MaHD = reader.GetInt32(0);
                HDTO.Xoa = reader.GetBoolean(1);
                HDTO.TrangThai = reader.GetBoolean(2);
                HDTO.Ban = reader.GetSqlMoney(3).ToString();
                HDTO.Tang = reader.GetSqlMoney(4).ToString();
                HDTO.Thu = reader.GetSqlMoney(5).ToString();
                HDTO.VAT = reader.GetSqlMoney(6).ToString();
                ds.Add(HDTO);
            }
            reader.Close();
            command = new SqlCommand("LINH34_HDDATHANHTOAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@TGBatDau", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGBatDau"].Value = bd;

            command.Parameters.Add("@TGKetThuc", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGKetThuc"].Value = kt;

            reader = command.ExecuteReader();
            while (reader.Read())
            {
                //MaHD, TenTang, TenBan, TenNV, ThoiGian, SoKhach from HOADON, TANG, BAN, NHANVIEN
                //MaHD,Xoa,TrangThai,'BAN', 'TANG', 'THU', 'VAT'
                HOADONDTO HDTO = new HOADONDTO();
                HDTO.MaHD = reader.GetInt32(0);
                HDTO.Xoa = reader.GetBoolean(1);
                HDTO.TrangThai = reader.GetBoolean(2);
                HDTO.Ban = reader.GetSqlMoney(3).ToString();
                HDTO.Tang = reader.GetSqlMoney(4).ToString();
                HDTO.Thu = reader.GetSqlMoney(5).ToString();
                HDTO.VAT = reader.GetSqlMoney(6).ToString();
                ds.Add(HDTO);
            }
            conn.Close();
            return ds;
        }
        public HOADONDTO DoanhThu(DateTime bd, DateTime kt)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH37_TONGHDDATHANHTOAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@TGBatDau", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGBatDau"].Value = bd;

            command.Parameters.Add("@TGKetThuc", System.Data.SqlDbType.DateTime);
            command.Parameters["@TGKetThuc"].Value = kt;

            SqlDataReader reader = command.ExecuteReader();
            HOADONDTO HDTO = new HOADONDTO();
            if (reader.Read())
            {
                //'BAN', 'TANG', 'THU', 'VAT'
                HDTO.Ban = reader.GetSqlMoney(0).ToString();
                HDTO.Tang = reader.GetSqlMoney(1).ToString();
                HDTO.Thu = reader.GetSqlMoney(2).ToString();
                HDTO.VAT = reader.GetSqlMoney(3).ToString();
            }
            reader.Close();
            conn.Close();
            return HDTO;
        }
        public HOADONDTO ThoiGianThanhToan(HOADONDTO HDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH41_THOIGIANTHANHTOAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaHD", System.Data.SqlDbType.Int);
            command.Parameters["@MaHD"].Value = HDTO.MaHD;

            SqlDataReader reader = command.ExecuteReader();
            HOADONDTO HD = new HOADONDTO();
            if (reader.Read())
            {
                HD.ThoiGian = reader.GetDateTime(0);
            }
            reader.Close();
            conn.Close();
            return HD;
        }
        public int XoaHD(HOADONDTO HDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH42_XOAHD", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaHD", System.Data.SqlDbType.Int);
            command.Parameters["@MaHD"].Value = HDTO.MaHD;

            int x = command.ExecuteNonQuery();
            conn.Close();
            return x;
        }
        //////////////////////////////////////////
    }
}
